# -*- encoding: utf-8 -*-
'''
@File   : openGaussIssueCollect.py
@Author : l00464502
@Date   : 2021-10-10
'''

import sys
import os
import datetime
import json

import requests
from bs4 import BeautifulSoup

from db_operate import DbOperate


class OpenGaussIssueCollect:
    priority_dict = {
        '4': ['严重', 10],
        '3': ['主要', 3],
        '2': ['次要', 1],
        '1': ['不重要', 0.1],
        '0': ['无优先级', 0]
    }
    author_dict = {
        "liumin35": "刘敏",
        "haomeng802": "郝梦",
        "zhangsuan96": "张欢",
        "peilinqian": "裴琳倩",
        "ningyali": "宁雅黎",
        "wan005": "万方",
        "daiguatutu": "侯少龙",
        "qumin201452": "屈敏",
        "gitwangxx": "王香香",
        "guoweibiao": "郭维彪",
        "sakuraaaa": "仇颖",
        "liang": "梁瑞",
        "yansong_lee": "李岩松",
        "xi-teming": "习特铭",
        "zhangao_za": "张翱",
        "kangyao59": "姚康",
        "bai_xiaoli": "白小丽",
        "chengjiaqi1": "成嘉琦",
        "wangke18": "王珂",
        "lizhuo9527": "李卓",
        "ssuai_lei666": "雷帅",
        "zhou-li-ya": "李亚洲",
        "xiaqi14": "夏琦",
        "lu-jianpei": "路建佩",
        "zou_jialiang050": "邹佳良",
        "li-xin12345": "李鑫",
        "lei-yanbin": "雷彦斌"
    }
    assign_dict = {
        "xue_meng_en": "薛蒙恩",
        "shirley_zhengx": "郑雪",
        "yansong_lee": "李岩松",
        "douxin": "窦欣",
        "chendong76": "陈栋",
        "buter": "张耀中",
        "zhaowenhao": "赵文浩",
        "gentle_hu": "胡正超",
        "justbk": "周斌",
        "zhang_xubo": "张旭博",
        "struggle_hw": "陈琛",
        "li_jianqiu": "李剑秋",
        "chenxiaobin19": "陈晓滨",
        "luo_zihao5524": "罗梓浩",
        "xiong_xjun": "熊小军",
        "totaj": "彭炯",
        "wuyuecsuan": "吴岳川",
        "xi-teming": "习特铭",
        "nifinity": "刘春",
        "kidhasdream": "甘洋",
        "mmmrf": "明瑞富"
    }

    def __init__(self, db_name, db_user, db_password, db_host, db_port):
        self.db_info = {'db_name': db_name,
                        'db_user': db_user,
                        'db_password': db_password,
                        'db_host': db_host,
                        'port': db_port}

    @staticmethod
    def get_requests(page=1):
        # 代理需单独修改
        proxies = {
            'http': 'http://[域账号]:[密码]@[代理地址如proxy.huawei.com:8080/]',
            'https': 'http://[域账号]:[密码]@[代理地址]'}
        # 请求内容
        headers = {
            "Content-type": "application/json"
        }
        params = {
            "access_token": "[码云gitee上申请的永久token]",
            "enterprise": "opengaussorg",
            "page": page,
            "per_page": 100,
            "state": "all"
        }
        url = "https://gitee.com/api/v5/enterprises/opengaussorg/issues"
        req = requests.get(url=url, params=params, headers=headers,
                           proxies=proxies, verify=False)
        content = req.content.decode('utf-8')
        soup = BeautifulSoup(content, 'html.parser')
        return soup

    @staticmethod
    def get_info():
        # 任务: 190708
        # 需求: 190709
        # 缺陷: 190710
        # 咨询：363814
        # 特性：543449
        # 开源实习：548518
        page = 1
        issue_infos = []
        issue_category = {
            "190708": ["任务", 0],
            "190709": ["需求", 0],
            "190710": ["缺陷", 0],
            "363814": ["咨询", 0],
            "543449": ["特性", 0],
            "548518": ["开源实习", 0]
        }
        while True:
            print(page)
            pi = json.loads(OpenGaussIssueCollect.get_requests(page).text, 
                            strict=False)
            print(pi)
            if not pi:
                print(issue_infos, issue_category)
                issue_category_form = []
                for issue_c in issue_category.keys():
                    print(f"('{issue_category[issue_c][0]}',"
                          f"'{issue_c}','{issue_category[issue_c][1]}')")
                    issue_category_form.append(
                        f"('{issue_category[issue_c][0]}',"
                        f"'{issue_c}','{issue_category[issue_c][1]}')")
                return [issue_infos, issue_category_form]
            else:
                for issue_info in pi:
                    tmp = ""
                    user_id = issue_info["user"]["login"]
                    assigee_id = issue_info["assignee"]["login"] \
                        if issue_info["assignee"] else ""
                    # 统计issue的分布
                    id_list = [int(key) for key in issue_category]
                    if issue_info["issue_type_detail"]["id"] in id_list:
                        issue_category[str(
                            issue_info["issue_type_detail"]["id"])][1] += 1
                    else:
                        issue_category[str(
                            issue_info["issue_type_detail"]["id"])][1] += 1
                    # 统计bug信息
                    if issue_info["issue_type_detail"]["id"] == 190710:
                        print(issue_info["repository"])
                        project = issue_info["repository"]["full_name"] \
                            if issue_info["repository"] else ""
                        issue = issue_info["number"]
                        title = issue_info["title"]
                        # \转义字符存在title中，需要处理，才能正确插入
                        if "\\" in title:
                            title = title.replace("\\", "\\\\")
                        # '存在title中，需要转义，才能正确插入
                        if "'" in title:
                            title = title.replace("'", "\\'")
                        priority = OpenGaussIssueCollect.priority_dict[
                            str(issue_info["priority"])][0]
                        di = OpenGaussIssueCollect.priority_dict[
                            str(issue_info["priority"])][1]
                        creator = OpenGaussIssueCollect.author_dict[user_id] \
                            if user_id in \
                               OpenGaussIssueCollect.author_dict.keys() \
                            else issue_info["user"]["name"]
                        if issue_info["assignee"]:
                            assignee = OpenGaussIssueCollect.assign_dict[
                                assigee_id] \
                                if assigee_id in \
                                   OpenGaussIssueCollect.assign_dict.keys() \
                                else issue_info["assignee"]["name"]
                        else:
                            assignee = ""
                        link = issue_info["html_url"]
                        # 时间格式转化
                        # 比如将2021-09-29T14:23:00+08:00格式转化为2021-09-29 14:23:00
                        time = issue_info["created_at"].replace("T", " ")[:-6]
                        time = datetime.datetime.strptime(time,
                                                          '%Y-%m-%d %H:%M:%S')
                        state = issue_info["issue_state"]
                        tmp += f"('{issue}', '{project}', E'{title}', " \
                               f"'{priority}', '{di}', '{creator}', " \
                               f"'{assignee}', '{link}', '{state}', '{time}')"
                    # 将每条数据归属到对应的仓库中
                    if tmp and tmp not in issue_infos:
                        issue_infos.append(tmp)
            page += 1

    def push_data_to_db(self):
        data = OpenGaussIssueCollect.get_info()
        if not data:
            print("注意，数据抓取失败，请分析")
            return

        # 数据库连接信息
        my_db = DbOperate(**self.db_info)
        my_db.conn_db()

        # 表信息
        table_list = ["ReportApp_issuesinfo", "ReportApp_issuescategory"]

        # 先清理表中数据
        for table in table_list:
            del_sql_cmd = "begin;\n"
            del_sql_cmd += f'delete from "{table}";\n'
            del_sql_cmd += "end;"
            my_db.exec_dml_sql(del_sql_cmd)

        # 给table_list中各表插入最新数据
        for table in table_list:
            insert_sql_cmd = "begin;\n"
            ind = table_list.index(table)
            # ReportApp_issuesinfo插入语句
            if ind == 0:
                insert_sql_cmd += f'insert into "{table}"(issue_id, ' \
                                  f'category, title, priority, di, creater, ' \
                                  f'assignee, link, state, create_date) ' \
                                  f'values '
            # ReportApp_issuescategory插入语句
            else:
                insert_sql_cmd += f'insert into "{table}"' \
                                  f'(issue_category, issue_category_id, ' \
                                  f'issue_count) values '
            insert_sql_cmd += ',\n'.join(data[ind])
            insert_sql_cmd += ";\nend;"
            my_db.exec_dml_sql(insert_sql_cmd)
            print(f"{table}表数据插入完成")

        # 关闭数据库连接
        my_db.close_conn()


if __name__ == '__main__':
    solution = OpenGaussIssueCollect("[数据库名]", "[用户名]", "[密码]",
                                     "[ip地址]", "[端口]")
    solution.push_data_to_db()
